﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace Housing
{
    public class Property
    {
        private int _pro_id;
        private int _pro_applyid;
        private int _pro_noid;
        private float _pro_securitie;
        private float _pro_estat;
        private float _pro_device;
        private float _pro_other;

        private int _effectDate;
        public int EffectDate
        {
            get { return _effectDate; }
            set { _effectDate = value; }
        }
        private int _applyid;
        public int Applyid
        {
            get { return _applyid; }
            set { _applyid = value; }
        }


        public int pro_id
        {
            get { return _pro_id; }
        }

        public int pro_applyid
        {
            get { return _pro_applyid; }
            set { _pro_applyid = value; }
        }
        public int pro_noid
        {
            get { return _pro_noid; }
            set { _pro_noid = value; }
        }
        public float pro_securitie
        {
            get { return _pro_securitie; }
            set { _pro_securitie = value; }
        }
        public float pro_estat
        {
            get { return _pro_estat; }
            set { _pro_estat = value; }
        }
        public float pro_device
        {
            get { return _pro_device; }
            set { _pro_device = value; }
        }
        public float pro_other
        {
            get { return _pro_other; }
            set { _pro_other = value; }
        }
        public Property()
        {
        }

        public void SetProperty(string applyid)
        {
              GetPropertyByProApplyId(applyid);
        }

        public Property(string applyid)
        {
            GetPropertyByProApplyId(applyid);
        }

        public void GetPropertyByProApplyId(string applyid)
        {
            string query = "SELECT * FROM Property WHERE pro_applyid=" + applyid;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, query);
            
            if (dr.Read())
            {
                _pro_id = (int)dr[0];
                _pro_applyid = Utils.GetNullableInt(dr[1]);
                _pro_noid = Utils.GetNullableInt(dr[2]);
                _pro_securitie = Utils.GetNullableFloat(dr[3]);
                _pro_estat = Utils.GetNullableFloat(dr[4]);
                _pro_device = Utils.GetNullableFloat(dr[5]);
                _pro_other = Utils.GetNullableFloat(dr[6]);
            }

            dr.Close();
        }

        public void GetPropertyByProNoid(string noid)
        {
            string query = "SELECT * FROM Property WHERE pro_noid=" + noid;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, query);

            if (dr.Read())
            {
                _pro_id = (int)dr[0];
                _pro_applyid = Utils.GetNullableInt(dr[1]);
                _pro_noid = Utils.GetNullableInt(dr[2]);
                _pro_securitie = Utils.GetNullableFloat(dr[3]);
                _pro_estat = Utils.GetNullableFloat(dr[4]);
                _pro_device = Utils.GetNullableFloat(dr[5]);
                _pro_other = Utils.GetNullableFloat(dr[6]);
            }

            dr.Close();
        }

        public int AddNewProperty(Property pro)
        {
            string query = "INSERT INTO Property(pro_applyid, pro_noid, pro_securitie, pro_estate, pro_device, pro_others,EffectDate)"
                           + "VALUES(" + pro.pro_applyid + "," + pro.pro_noid + "," + pro.pro_securitie + ","
                           + pro.pro_estat + "," + pro.pro_device + "," + pro.pro_other + ","+pro.EffectDate+")";

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }

        public int UpdatePropertyByProId(Property pro)
        {
            string query = "UPDATE Property SET pro_applyid=" + pro.pro_applyid + ",pro_noid=" + pro.pro_noid + ","
                           + "pro_securitie=" + pro.pro_securitie + ",pro_estate=" + pro.pro_estat + ","
                           + "pro_device=" + pro.pro_device + ",pro_others=" + pro.pro_other
                           + "WHERE pro_id=" + pro.pro_id;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }

        public DataSet GetAllProperty()
        {
            string query = "SELECT * FROM Property";

            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, query);
            
            return ds;
        }

        public int DeletePropertyByProId(int proId)
        {
            string query = "DELETE FROM Property WHERE pro_id=" + proId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }


    }

}